﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace Supersense.db
{
    /// <summary>
    /// Db 的摘要说明
    /// </summary>
    public class Database
    {
        public SqlConnection conn = new SqlConnection();

        private string _er;

        public string er
        {
            get { return _er; }

        }

        public Database()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString();
        }

        public void db_open()
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString();
                    conn.Open();
                }
            }
            catch (Exception ex)
            {
                //System.Web.HttpContext.Current.Response.Redirect(System.Configuration.ConfigurationSettings.AppSettings["conn_admin"].ToString());
                System.Web.HttpContext.Current.Response.Write(ex.Message);
                System.Web.HttpContext.Current.Response.End();
            }

        }
        /// <summary>
        /// 执行非返回型查询语句
        /// </summary>
        /// <returns>是否执行成功</returns>
        public bool executesql(String sql)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            this.db_open();
            try
            {
                cmd.ExecuteNonQuery();
                this.db_close();
                return true;
            }
            catch (Exception ex)
            {
                this.db_close();
                System.Web.HttpContext.Current.Response.Write(ex.Message);
                System.Web.HttpContext.Current.Response.End();
                return false;
            }

        }

        /// <summary>
        /// 执行返回datareader的数据库查询
        /// </summary>
        /// <returns>datareader对象</returns>
        public SqlDataReader executereader(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            this.db_open();
            SqlDataReader dr;
            try
            {
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//参数表示关闭datagrid对象就会关闭connection对象

                if (dr.HasRows == true)
                {
                    return dr;
                    dr = null;
                }
                else
                {
                    _er = "数据为空";
                    return null;
                }
            }
            catch
            {
                _er = "语句错误";
                return null;
            }
        }

        /// <summary>
        /// 将sql语句返回为dataview
        /// </summary>
        /// <returns>dataview对象</returns>
        public DataView executedv(string sql)
        {
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(sql, this.conn);
            DataSet ds = new DataSet();
            this.db_open();
            try
            {
                da.Fill(ds, "tb");
                DataView dv = new DataView(ds.Tables["tb"]);
                if (dv.Count > 0)
                {
                    this.db_close();
                    return dv;
                }
                else
                {
                    this.db_close();
                    _er = "数据出错";
                    return null;
                }
            }
            catch
            {
                _er = "数据出错";
                return null;
            }
            finally
            {
                this.db_close();
            }
        }

        public DataTable executedt(string sql)
        {
            DataView dv = this.executedv(sql);
            DataTable dt;
            if (dv != null)
            {
                dt = dv.Table;
            }
            else
            {
                dt = null;
            }
            
            return dt;
        }


        public string sql2str(string sql,string def)
        {
            string rr = "";
            SqlCommand cmd = new SqlCommand(sql, conn);
            System.Data.SqlClient.SqlDataReader dr;
            this.db_open();
            try
            {
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (dr.HasRows)
                {
                    dr.Read();
                    rr = dr[0].ToString();
                    dr.Close();
                    this.db_close();
                }
            }
            catch
            {
                rr = "";
            }
            finally { this.db_close(); }
            if (rr == "")
                rr = def;
            return rr;
        }


        public void db_close()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        ~Database()
        {
            if (conn.State==ConnectionState.Open)
            conn.Close();
        }
    }
}

